
use "$path\Intermediary Data\DataReg_SP95", clear

drop classif_num autoroute delta_lprix_ht eurusd gasoline_rotterdam
drop if j3==5 /* delete sundays **/
egen daten=group(date_maj_num)
xtset id daten

gen num_obs=_n-1

bysort id (daten): gen dlog_prix_ht=log(prix_ht[_n])-log(prix_ht[_n-1])

merge m:m id_pdv using "$path\Intermediary Data\id_dpv_idb.dta"
keep if _m==3
drop _m


save "$path\Intermediary Data\temp_sp95.dta", replace
use "$path\Intermediary Data\temp_sp95.dta", clear

**** Step 3.1: Save 10 datasets


bys idb daten: egen dlog_prix_htm=mean(dlog_prix_ht)
duplicates drop idb daten, force 
keep idb daten dlog_prix_htm
drop if idb==.
drop if daten==.

save "$path\Intermediary Data\dprix_idb_sp95.dta", replace

foreach i of numlist 1/15 {

use "$path\Intermediary Data\dprix_idb_sp95.dta"
keep idb daten dlog_prix_htm
rename idb nid`i'
rename dlog_prix_htm dlog_prix_ht_nid`i'
save "$path\Intermediary Data\nid`i'_sp95.dta", replace
}

use "$path\Intermediary Data\temp_sp95.dta", clear

**** Step 3.2: identify  average variation among the 10 closest stations
merge m:1 idb using "$path\Intermediary Data\NN10_id_pdv_forlocalshocks.dta"
keep if _merge==3
drop _merge

drop km_to* 

merge m:1 nid1 daten using "$path\Intermediary Data\nid1_sp95.dta"
drop if _merge==2
drop _merge

merge m:1 nid2 daten using "$path\Intermediary Data\nid2_sp95.dta"
drop if _merge==2
drop _merge

merge m:1 nid3 daten using "$path\Intermediary Data\nid3_sp95.dta"
drop if _merge==2
drop _merge

merge m:1 nid4 daten using "$path\Intermediary Data\nid4_sp95.dta"
drop if _merge==2
drop _merge


merge m:1 nid5 daten using "$path\Intermediary Data\nid5_sp95.dta"
drop if _merge==2
drop _merge


merge m:1 nid6 daten using "$path\Intermediary Data\nid6_sp95.dta"
drop if _merge==2
drop _merge

merge m:1 nid7 daten using "$path\Intermediary Data\nid7_sp95.dta"
drop if _merge==2
drop _merge


merge m:1 nid8 daten using "$path\Intermediary Data\nid8_sp95.dta"
drop if _merge==2
drop _merge


merge m:1 nid9 daten using "$path\Intermediary Data\nid9_sp95.dta"
drop if _merge==2
drop _merge


merge m:1 nid10 daten using "$path\Intermediary Data\nid10_sp95.dta"
drop if _merge==2
drop _merge



*** Average of non-missing values


egen local_shock10=rmean(dlog_prix_ht_nid1 dlog_prix_ht_nid2 dlog_prix_ht_nid3 dlog_prix_ht_nid4 dlog_prix_ht_nid5 ///
dlog_prix_ht_nid6 dlog_prix_ht_nid7 dlog_prix_ht_nid8 dlog_prix_ht_nid9 dlog_prix_ht_nid10)



save "$path\Intermediary Data\localshock_dataforReg_sp95.dta", replace


	 

drop dlog_prix_ht longitude latitude nid* dlog_prix_ht_nid1-dlog_prix_ht_nid10
*drop prix


drop if j3==4
drop daten
egen daten=group(date_maj_num)
drop date_maj_num
xtset id daten
capture drop d_gasoliner
gen d_gasoliner=ln(gasoline_rotterdam_euro)-ln(l1.gasoline_rotterdam_euro)

bysort id (daten):  gen dprix1=log(prix_ht[_n])-log(prix_ht[_n-1])
bys daten: egen m_dprix=mean(dprix1)
drop num_obs
gen num_obs=_n-1
save "$path\Intermediary Data\localshock_dataforReg_sp95_v2.dta", replace

